MYDB=$DB_WEATHER
INPUT_FOLDER=../data/raw_data/weather
INPUT_GEO_FOLDER=../data/raw_data/geography
OUTPUT_FOLDER=../data/data_csv
GEO_YEAR=2017
ADMIN_SHAPE_FILE=$INPUT_GEO_FOLDER/shapeFile/CantonId.shp

TMP1_FILE=tmp1.csv
TMP2_FILE=tmp2.csv
TMP3_FILE=tmp3.csv

WEATHER_PROCESSED_FILE=$INPUT_FOLDER/weather.csv
VORONOI_SHAPE_FILE=$INPUT_FOLDER/Weather_Voronoi/Voronoi_output.shp
     # Shape file of Voronoi polygons in France metropolitaine, where each polygon corresponds to a weather station

########## Import CSV File that contains the weather data ##########
importCSVFile $MYDB $WEATHER_PROCESSED_FILE weather0

##### Add date-specific fields (day/week/month/...) #####
sqlite3 $MYDB "create table weather as \
    select a.STATION, a.date, dayNum, weekNum, yearMonth, a.PRCP, a.TAVG, substr(a.date,6,5) as dateMonthDay \
    from weather0 a, mydays b where a.date = b.date;"
dropTables $MYDB weather0

### Calculate "average weather" for each day of the week at each station, based on years 2008-2012
sqlite3 $MYDB "create table avgWeather as \
    select STATION, dateMonthDay, AVG(PRCP) as usualPRCP, AVG(TAVG) as usualTAVG \
    from weather \
    where date < '2013-01-01' \
    group by STATION, dateMonthDay;"

### Merge weather with average weather so they are all in one table (think there is the current weather, and the "normale saisonniere")
sqlite3 $MYDB "create table weather2 as \
    select a.*, b.usualPRCP, b.usualTAVG \
    from weather a, avgWeather b \
    where a.STATION=b.STATION and a.dateMonthDay=b.dateMonthDay;"
dropTables $MYDB weather avgWeather
renameTable $MYDB weather2 weather


########## Calculate weather for each geographic entity by taking a weighted average across weather stations, ##########
########## where the weight is the percentage of the area of the geographic entity ##########
########## that is closest to each station (intersection with each Voronoi polygon) ##########
# Calculate for each geographic unit, the area of its intersection with each of the Voronoi polygons.
python3 getIntersectionAreas.py $ADMIN_SHAPE_FILE CantonId $VORONOI_SHAPE_FILE PointId $TMP3_FILE

# Import CSV with intersection areas
importCSVFile $MYDB $TMP3_FILE CantonId_Voronoi_areas
rm $TMP3_FILE

# Import CSV with total area of entities
importCSVFile $MYDB $INPUT_GEO_FOLDER/canton_areas.csv Areas_CantonId

# Calculate the weight of each polygon for each geographic unit.
sqlite3 $MYDB "create table CantonId_Voronoi_weights as \
    select a.CantonId, a.PointId as STATION, a.IntersectionArea/b.Area as weight \
    from CantonId_Voronoi_areas a, Areas_CantonId b \
    where a.CantonId = b.CantonId;"

# Calculate average temperature and precipitation per day in each geographic unit as weighted average using these weights.
sqlite3 $MYDB "create table tmp_day as \
    select CantonId, dayNum, weekNum, yearMonth, \
    sum(weight*PRCP) as PRCP, sum(weight*TAVG) as TAVG, sum(weight*usualPRCP) as usualPRCP, sum(weight*usualTAVG) as usualTAVG \
    from CantonId_Voronoi_weights a, weather b where a.STATION = b.STATION group by CantonId, dayNum, weekNum, yearMonth;"

# Aggregate at week level
sqlite3 $MYDB "create table tmp_week as \
    select CantonId, weekNum, sum(PRCP) as PRCP, avg(TAVG) as TAVG, sum(usualPRCP) as usualPRCP, avg(usualTAVG) as usualTAVG \
    from tmp_day group by CantonId, weekNum;"

# Create indexes to speed up merging with Scope
sqlite3 $MYDB "create index if not exists index_tmp_day   ON tmp_day   (CantonId, dayNum);"
sqlite3 $MYDB "create index if not exists index_tmp_week  ON tmp_week  (CantonId, weekNum);"

# Merge with referentiel
for TIME_LEVEL in week day ; do
    echo "CantonId - ${TIME_LEVEL}..."
    if [ $TIME_LEVEL == "day" ]; then TIME_ID=dayNum; fi
    if [ $TIME_LEVEL == "week" ]; then TIME_ID=weekNum; fi
    sqlite3 $MYDB "create table Weather_CantonId_$TIME_LEVEL as select a.CantonId, a.$TIME_ID, b.PRCP, b.TAVG, b.usualPRCP, b.usualTAVG \
        from Scope_CantonId_$TIME_LEVEL a left join tmp_$TIME_LEVEL b on a.CantonId = b.CantonId and a.$TIME_ID=b.$TIME_ID;"
done

# Clean up
dropTables $MYDB CantonId_Voronoi_areas CantonId_Voronoi_weights
dropTables $MYDB tmp_day tmp_week Areas_CantonId
# NOTE: somehow, the regions (and other geographic entities) in the shape files have some non-empty intersections (they are a bit overlapping).
# This explains why the sum of the areas of the regions is slightly greater than the sum of the areas of the Voronoi polygons.
# That is not a big deal.

# Final clean-up
dropTables $MYDB weather mydays

### Clean up
for TIME_LEVEL in week day ; do
    dropTables $MYDB Scope_CantonId_${TIME_LEVEL}
done
sqlite3 $MYDB "VACUUM;"
